﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace NCC_SOP.app_codes
{
    public static class sqlQuery
    {
        public static string createNewRole = "insert into rolesDef Values(@roleName)";
        public static string getAllRoles = "select roleName from rolesDef";
        public static string getUsersInReportRole1 = "select UserId from roleUserMapping where roleID = @roleID";
        public static string getUsersinReportRole2 = "select userName from aspnet_Users where UserId = @userID";
        public static string getReportRolesForUser1 = "select roleID from roleUserMapping where UserId= @userID";
        public static string getReportRolesForUser2 = "select roleName from rolesDef where roleID= @roleID";
        public static string getUserID = "select UserId from aspnet_Users where UserName= @userName";
        public static string getRoleID = "select roleID from rolesDef where roleName = @role";
        public static string addUserIntoReportRole = "Insert into roleUserMapping VALUES(@userID,@roleID)";
        public static string removeUserFromReportRole = "delete from roleUserMapping where UserId= @userID AND roleId = @roleID";
        public static string deleteRole1 = "select roleID from rolesDef WHERE roleName = @role";
        public static string deleteRole2 = "delete from roleReportMapping WHERE roleID= @roleID";
        public static string deleteRole3 = "delete from roleUserMapping WHERE roleID= @roleID";
        public static string deleteRole4 = "delete from rolesDef WHERE roleID= @roleID";
        public static string getAllHotSpotIDs = "select p_id from Mapping";
        public static string getHotSpotDetails = "select * from Mapping where p_id = @p_id";
        public static string getSelectedCancer = "select Label from Mapping where p_id= @p_id";
        public static string getHistologyList = "select DISTINCT Detail_Description from ['icd mapping file$'] where Label= @label";
        public static string getStaging = "select staging, detail, type from ['staging files$'] where CA= @ca AND version= @ver";
        public static string getStagingTypes = "select DISTINCT type from ['staging files$'] where CA= @ca";
        public static string getAllDepartments = "select alias from departments";
        public static string getDepartmentsForUser1 = "select departmentID from staffDepartmentMapping where userID= @userID";
        public static string getDepartmentsForUser2 = "select alias from departments where departmentID= @departmentID";
        public static string getDepartmentID = "select departmentID from departments where alias = @alias";
    }
}